﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoyoEden.Models;
using System.Data.Common;
using System.Data;

namespace CoyoEden.Providers
{
	public partial class DbPortalProvider
	{

        public override Topic SelectTopic(int id)
		{
			Topic topic = new Topic();
			using (DbConnection conn = MyDbFactory.CreateConnection()) {//using1
				conn.ConnectionString = connStringStatic;
				using(DbCommand cmd=conn.CreateCommand()){//using2
					//TODO:These sql clause should be maintained in a XML document,and should be cached.
					StringBuilder sql = new StringBuilder();
					sql.Append("SELECT TopicID,Title,Description,Content,DateCreated,DateModified,Author,IsPublished,");
					sql.AppendFormat("IsCommentEnabled,IsValuable,IsApproved,Icon FROM {0}{1} WHERE TopicID={2}id",tablePrefix,"Topics",parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = id;
					cmd.Parameters.Add(dpID);

					conn.Open();
					//1,Load basic topic info
					using(DbDataReader rdr=cmd.ExecuteReader()){//using3
						if (rdr.HasRows) {
							rdr.Read();
							topic.Id = rdr.GetInt32(0);
							topic.Title = rdr.GetString(1);
							topic.Description = rdr.GetString(2);
							topic.Content = rdr.GetString(3);
							topic.DateCreated = rdr.GetDateTime(4);
							topic.DateModified = rdr.GetDateTime(5);
							topic.Author = rdr.GetString(6);
							topic.IsPublished = rdr.GetBoolean(7);
							topic.IsCommentEnabled = rdr.GetBoolean(8);
							topic.IsValuable = rdr.GetBoolean(9);
							topic.IsApproved = rdr.GetBoolean(10);
							topic.Icon = rdr.GetString(11);
						}
					}//endof using3
					
					//2,load communities
					sql.Replace(sql.ToString(), "");
					sql.AppendFormat("SELECT CommunityID FROM {0}CommunityTopic WHERE TopicID={1}id",tablePrefix,parmPrefix);
					cmd.CommandText = sql.ToString();
					using(DbDataReader rdr=cmd.ExecuteReader()){//using4
						while (rdr.Read()) {
							int key = rdr.GetInt32(0);
							var community = Community.GetCommunity(key);
							if (community != null) {
								topic.Communities.Add(community);
							}
						}
					}//endof using4

					//3,load comments
					sql.Replace(sql.ToString(),"");
					sql.Append("SELECT Id, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved ");
					sql.AppendFormat("FROM {0}TopicComment WHERE TopicID ={1}id",tablePrefix,parmPrefix);
					cmd.CommandText = sql.ToString();
					using (DbDataReader rdr = cmd.ExecuteReader())//using5
					{
						while (rdr.Read()) {
							Comment comment = new Comment();
							comment.Id = rdr.GetInt32(0);
							comment.DateCreated = rdr.GetDateTime(1);
							comment.Author = rdr.GetString(2);
							comment.Email = rdr.GetString(3);
							if (!rdr.IsDBNull(4)) {
								Uri website;
								if (Uri.TryCreate(rdr.GetString(4), UriKind.Absolute, out website)) {
									comment.Website = website;
								}
							}
							comment.Content = rdr.GetString(5);
							comment.Country = rdr.GetString(6);
							comment.IP = rdr.GetString(7);
							comment.IsApproved = true;
							topic.Comments.Add(comment);
						}
					}//endof using5
					topic.Comments.Sort();
					
					//load tags
					topic.Tags.AddRange(LoadTopicTags(topic,conn));
					topic.Tags.MarkOld();

				}//endof using2
			}//endof using1
			return topic;
		}
        public override void InsertTopic(Topic item)
		{
			using(DbConnection conn=MyDbFactory.CreateConnection()){//using1
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using(DbCommand cmd=conn.CreateCommand()){//using2
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("INSERT INTO {0}Topics (Title,Description,Content,DateCreated,DateModified,",tablePrefix);
					sql.Append("Author,IsPublished,IsCommentEnabled,IsValuable,IsApproved,Icon) VALUES (");
					sql.AppendFormat("{0}title,{0}desc,{0}content,{0}created,{0}modified,{0}author,",parmPrefix);
					sql.AppendFormat("{0}published,{0}commentEnabled,{0}valuable,{0}approved,{0}icon)",parmPrefix);
					sql.AppendFormat(";SELECT TopicID FROM {0}Topics WHERE Title={1}title AND DateCreated={1}created AND Author={1}author",tablePrefix,parmPrefix);
					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpTitle = MyDbFactory.CreateParameter();
					dpTitle.ParameterName = parmPrefix + "title";
					dpTitle.Value = item.Title;
					cmd.Parameters.Add(dpTitle);

					DbParameter dpDesc = MyDbFactory.CreateParameter();
					dpDesc.ParameterName = parmPrefix + "desc";
					dpDesc.Value = item.Description;
					cmd.Parameters.Add(dpDesc);

					DbParameter dpContent = MyDbFactory.CreateParameter();
					dpContent.ParameterName = parmPrefix + "content";
					dpContent.Value = item.Content;
					cmd.Parameters.Add(dpContent);

					DbParameter dpCreated = MyDbFactory.CreateParameter();
					dpCreated.ParameterName = parmPrefix + "created";
					dpCreated.Value = item.DateCreated;
					cmd.Parameters.Add(dpCreated);

					DbParameter dpModified = MyDbFactory.CreateParameter();
					dpModified.ParameterName = parmPrefix + "modified";
					dpModified.Value = item.DateModified;
					cmd.Parameters.Add(dpModified);

					DbParameter dpAuthor = MyDbFactory.CreateParameter();
					dpAuthor.ParameterName = parmPrefix + "author";
					dpAuthor.Value = item.Author;
					cmd.Parameters.Add(dpAuthor);

					DbParameter dppublished = MyDbFactory.CreateParameter();
					dppublished.ParameterName = parmPrefix + "published";
					dppublished.Value = item.IsPublished;
					cmd.Parameters.Add(dppublished);

					DbParameter dpcommentEnabled = MyDbFactory.CreateParameter();
					dpcommentEnabled.ParameterName = parmPrefix + "commentEnabled";
					dpcommentEnabled.Value = item.IsCommentEnabled;
					cmd.Parameters.Add(dpcommentEnabled);

					DbParameter dpvaluable = MyDbFactory.CreateParameter();
					dpvaluable.ParameterName = parmPrefix + "valuable";
					dpvaluable.Value = item.IsValuable;
					cmd.Parameters.Add(dpvaluable);

					DbParameter dpapproved = MyDbFactory.CreateParameter();
					dpapproved.ParameterName = parmPrefix + "approved";
					dpapproved.Value = item.IsApproved;
					cmd.Parameters.Add(dpapproved);

					DbParameter dpIcon = MyDbFactory.CreateParameter();
					dpIcon.ParameterName = parmPrefix + "icon";
					dpIcon.Value = item.Icon;
					cmd.Parameters.Add(dpIcon);

					var topicID = cmd.ExecuteScalar();
					item.Id = Convert.ToInt32(topicID);
				}//endof using2

				//update communities
				UpdateCommunities(item,conn);
				UpdateComments(item,conn);
				UpdateTopicTags(item, conn);
			}//endof using1
		}

        public override void UpdateTopic(Topic item)
		{
			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("UPDATE {0}Topics ",tablePrefix);
					sql.AppendFormat("SET Title = {0}title, Description = {0}desc, Content = {0}content, ",parmPrefix);
					sql.AppendFormat("DateCreated = {0}created, DateModified = {0}modified, Author = {0}Author, ",parmPrefix);
					sql.AppendFormat("IsPublished = {0}published, IsCommentEnabled = {0}commentEnabled, ",parmPrefix);
					sql.AppendFormat("IsValuable = {0}valuable, IsApproved = {0}approved, Icon = {0}icon ",parmPrefix);
					sql.AppendFormat("WHERE TopicID = {0}id",parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id.ToString();
					cmd.Parameters.Add(dpID);

					DbParameter dpTitle = MyDbFactory.CreateParameter();
					dpTitle.ParameterName = parmPrefix + "title";
					dpTitle.Value = item.Title;
					cmd.Parameters.Add(dpTitle);

					DbParameter dpDesc = MyDbFactory.CreateParameter();
					dpDesc.ParameterName = parmPrefix + "desc";
					dpDesc.Value = item.Description ?? "";
					cmd.Parameters.Add(dpDesc);

					DbParameter dpContent = MyDbFactory.CreateParameter();
					dpContent.ParameterName = parmPrefix + "content";
					dpContent.Value = item.Content;
					cmd.Parameters.Add(dpContent);

					DbParameter dpCreated = MyDbFactory.CreateParameter();
					dpCreated.ParameterName = parmPrefix + "created";
					dpCreated.Value = item.DateCreated.AddHours(-PortalSettings.Instance.Timezone);
					cmd.Parameters.Add(dpCreated);

					DbParameter dpModified = MyDbFactory.CreateParameter();
					dpModified.ParameterName = parmPrefix + "modified";
					if (item.DateModified == new DateTime())
						dpModified.Value = DateTime.Now;
					else
						dpModified.Value = item.DateModified.AddHours(-PortalSettings.Instance.Timezone);
					cmd.Parameters.Add(dpModified);

					DbParameter dpAuthor = MyDbFactory.CreateParameter();
					dpAuthor.ParameterName = parmPrefix + "author";
					dpAuthor.Value = item.Author ?? "";
					cmd.Parameters.Add(dpAuthor);

					DbParameter dpPublished = MyDbFactory.CreateParameter();
					dpPublished.ParameterName = parmPrefix + "published";
					dpPublished.Value = item.IsPublished;
					cmd.Parameters.Add(dpPublished);

					DbParameter dpCommentEnabled = MyDbFactory.CreateParameter();
					dpCommentEnabled.ParameterName = parmPrefix + "commentEnabled";
					dpCommentEnabled.Value = item.IsCommentEnabled;
					cmd.Parameters.Add(dpCommentEnabled);

					DbParameter dpValuable = MyDbFactory.CreateParameter();
					dpValuable.ParameterName = parmPrefix + "valuable";
					dpValuable.Value = item.IsValuable;
					cmd.Parameters.Add(dpValuable);

					DbParameter dpApproved = MyDbFactory.CreateParameter();
					dpApproved.ParameterName = parmPrefix + "approved";
					dpApproved.Value = item.IsApproved;
					cmd.Parameters.Add(dpApproved);

					DbParameter dpIcon = MyDbFactory.CreateParameter();
					dpIcon.ParameterName = parmPrefix + "icon";
					dpIcon.Value = item.Icon;
					cmd.Parameters.Add(dpIcon);

					cmd.ExecuteNonQuery();
				}

				//update communities
				UpdateCommunities(item, conn);
				UpdateComments(item, conn);
				UpdateTopicTags(item, conn);
			}
		}

        public override void DeleteTopic(Topic item)
		{
			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("DELETE FROM {0}CommunityTopic WHERE TopicID = {1}id;",tablePrefix,parmPrefix);
					sql.AppendFormat("DELETE FROM {0}TopicComment WHERE TopicID = {1}id;",tablePrefix,parmPrefix);
					sql.AppendFormat("DELETE FROM {0}TopicTag WHERE TopicID={1}id;",tablePrefix,parmPrefix);
					sql.AppendFormat("DELETE FROM {0}Topics WHERE TopicID = {1}id;",tablePrefix,parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id.ToString();
					cmd.Parameters.Add(dpID);

					cmd.ExecuteNonQuery();
				}
			}
		}

        public override List<Topic> FillTopics()
		{
			List<Topic> topics = new List<Topic>();
			List<int> topicIDs = new List<int>();

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;

				using (DbCommand cmd = conn.CreateCommand())
				{
					string sqlQuery = "SELECT TopicID FROM " + tablePrefix + "Topics ";
					cmd.CommandText = sqlQuery;
					cmd.CommandType = CommandType.Text;

					conn.Open();

					using (DbDataReader rdr = cmd.ExecuteReader())
					{
						while (rdr.Read())
						{
							topicIDs.Add(rdr.GetInt32(0));
						}
					}
				}
			}

			foreach (int id in topicIDs)
			{
				topics.Add(Topic.Load(id));
			}

			topics.Sort();
			return topics;
		}

		#region helper methods
		private void UpdateComments(Topic item, DbConnection conn)
		{
			StringBuilder sql = new StringBuilder();
			sql.AppendFormat( "DELETE FROM {0}TopicComment WHERE TopicID ={1}id",tablePrefix,parmPrefix);
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql.ToString();
				cmd.CommandType = CommandType.Text;
				DbParameter dpID = MyDbFactory.CreateParameter();
				dpID.ParameterName = parmPrefix + "id";
				dpID.Value = item.Id;
				cmd.Parameters.Add(dpID);
				cmd.ExecuteNonQuery();

				foreach (Comment comment in item.Comments)
				{
					sql.Replace(sql.ToString(),"");
					sql.AppendFormat("INSERT INTO {0}TopicComment (TopicID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved) ",tablePrefix);
					sql.AppendFormat("VALUES ({0}id, {0}date, {0}author, {0}email, {0}website, {0}comment, {0}country, {0}ip, {0}isapproved); ",parmPrefix);
					sql.AppendFormat("SELECT Id FROM {0}TopicComment WHERE TopicID={1}id AND CommentDate={1}date AND Author={1}author AND Comment={1}comment AND Ip={1}ip;",tablePrefix,parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.Parameters.Clear();

					DbParameter dpPostID = MyDbFactory.CreateParameter();
					dpPostID.ParameterName = parmPrefix + "id";
					dpPostID.Value = item.Id;
					cmd.Parameters.Add(dpPostID);

					DbParameter dpCommentDate = MyDbFactory.CreateParameter();
					dpCommentDate.ParameterName = parmPrefix + "date";
					dpCommentDate.Value = comment.DateCreated;
					cmd.Parameters.Add(dpCommentDate);

					DbParameter dpAuthor = MyDbFactory.CreateParameter();
					dpAuthor.ParameterName = parmPrefix + "author";
					dpAuthor.Value = comment.Author;
					cmd.Parameters.Add(dpAuthor);

					DbParameter dpEmail = MyDbFactory.CreateParameter();
					dpEmail.ParameterName = parmPrefix + "email";
					dpEmail.Value = comment.Email ?? "";
					cmd.Parameters.Add(dpEmail);

					DbParameter dpWebsite = MyDbFactory.CreateParameter();
					dpWebsite.ParameterName = parmPrefix + "website";
					if (comment.Website == null)
						dpWebsite.Value = string.Empty;
					else
						dpWebsite.Value = comment.Email;
					cmd.Parameters.Add(dpWebsite);

					DbParameter dpContent = MyDbFactory.CreateParameter();
					dpContent.ParameterName = parmPrefix + "comment";
					dpContent.Value = comment.Content;
					cmd.Parameters.Add(dpContent);

					DbParameter dpCountry = MyDbFactory.CreateParameter();
					dpCountry.ParameterName = parmPrefix + "country";
					dpCountry.Value = comment.Country ?? string.Empty;
					cmd.Parameters.Add(dpCountry);

					DbParameter dpIP = MyDbFactory.CreateParameter();
					dpIP.ParameterName = parmPrefix + "ip";
					dpIP.Value = comment.IP ?? string.Empty;
					cmd.Parameters.Add(dpIP);

					DbParameter dpIsApproved = MyDbFactory.CreateParameter();
					dpIsApproved.ParameterName = parmPrefix + "isapproved";
					dpIsApproved.Value = comment.IsApproved;
					cmd.Parameters.Add(dpIsApproved);

					var cmID = cmd.ExecuteScalar();
					comment.Id = Convert.ToInt32(cmID);
				}
			}
		}

		private void UpdateCommunities(Topic item, DbConnection conn)
		{
			StringBuilder sql = new StringBuilder();
			sql.AppendFormat("DELETE FROM {0}CommunityTopic WHERE TopicID={1}id", tablePrefix, parmPrefix);
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.Text;
				cmd.CommandText = sql.ToString();
				DbParameter dpID = MyDbFactory.CreateParameter();
				dpID.ParameterName = parmPrefix + "id";
				dpID.Value = item.Id;
				cmd.Parameters.Add(dpID);
				cmd.ExecuteNonQuery();

				//insert community
				DbParameter dpCID =MyDbFactory.CreateParameter();
				dpCID.ParameterName = parmPrefix + "cid";
				item.Communities.ForEach(c => {
					sql.Replace(sql.ToString(),"");
					sql.AppendFormat("INSERT INTO {0}CommunityTopic (CommunityID,TopicID) VALUES ({1}cid,{1}id);",tablePrefix,parmPrefix);
					cmd.CommandText = sql.ToString();
					if (!cmd.Parameters.Contains(dpCID))
					{
						dpCID.Value = c.Id;
						cmd.Parameters.Add(dpCID);
					}
					else {
						cmd.Parameters[dpCID.ParameterName].Value = c.Id;
					}
					cmd.ExecuteNonQuery();
				});
			}
		}
		private List<string> LoadTopicTags(Topic item,DbConnection conn) {
			List<string> retVal = new List<string>();
			StringBuilder sql = new StringBuilder();
			sql.AppendFormat("SELECT Tag FROM {0}TopicTag WHERE TopicID={1}id",tablePrefix,parmPrefix);
			using(DbCommand cmd=conn.CreateCommand()){//using1
				cmd.CommandText = sql.ToString();
				cmd.CommandType = CommandType.Text;

				DbParameter dpID = MyDbFactory.CreateParameter();
				dpID.ParameterName = parmPrefix + "id";
				dpID.Value = item.Id;
				cmd.Parameters.Add(dpID);

				using(DbDataReader rdr=cmd.ExecuteReader()){//using2
					while (rdr.Read()) {
						retVal.Add(rdr.GetString(0));
					}
				}//endof using2

			}//endof Using1
			return retVal;
		}
		private void UpdateTopicTags(Topic item,DbConnection conn) {
			StringBuilder sql = new StringBuilder();
			sql.AppendFormat("DELETE FROM {0}TopicTag WHERE TopicID={1}id;",tablePrefix,parmPrefix);
			
			using(DbCommand cmd=conn.CreateCommand()){//using1
				cmd.CommandType = CommandType.Text;
				cmd.CommandText = sql.ToString();

				DbParameter dpID = MyDbFactory.CreateParameter();
				dpID.ParameterName = parmPrefix + "id";
				dpID.Value = item.Id;
				cmd.Parameters.Add(dpID);

				cmd.ExecuteNonQuery();

				sql.Replace(sql.ToString(),"");
				sql.AppendFormat("INSERT INTO {0}TopicTag (TopicID,Tag) VALUES ({1}id,{1}tag);", tablePrefix, parmPrefix);
				DbParameter dpTag = MyDbFactory.CreateParameter();
				dpTag.ParameterName = parmPrefix + "tag";
				cmd.Parameters.Add(dpTag);

				item.Tags.ForEach(obj => {
					dpTag.Value = obj;
					cmd.CommandText = sql.ToString();
					cmd.ExecuteNonQuery();
				});

			}//endof using1
		}
		#endregion

	}
}
